package com.homestead.reader;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.xml.sax.*;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

/**
 * @author 1
 */
public class ExcelReader {
    private OPCPackage pkg;

    public ExcelReader(InputStream inputStream) throws Exception {
        pkg = OPCPackage.open(inputStream);
    }

    public  List<List<String>> getDataList() throws Exception {
        XSSFReader reader = new XSSFReader(pkg);
        SharedStringsTable sst = reader.getSharedStringsTable();
        XMLReader parser = XMLReaderFactory.createXMLReader();
        //防xxe注入
        parser.setFeature("http://apache.org/xml/features/disallow-doctype-decl",true);
        List<List<String>> dataList = new ArrayList<>();
        ContentHandler handler = new SheetHandler(sst, dataList);
        parser.setContentHandler(handler);
        Iterator<InputStream> sheetsData = reader.getSheetsData();
        while (sheetsData.hasNext()) {
            InputStream sheet = sheetsData.next();
            InputSource sheetSource = new InputSource(sheet);
            parser.parse(sheetSource);
            sheet.close();
        }
        pkg.close();
        return dataList;
    }

    public List<String> getHeader() throws Exception {
        List<String> header = new ArrayList<>();
        XSSFWorkbook xwb = new XSSFWorkbook(pkg);
        Sheet sheet = xwb.getSheetAt(0);
        Row row = sheet.getRow(0);

        Iterator<Cell> iterator = row.cellIterator();
        while (iterator.hasNext()) {
            Cell cell = iterator.next();
            header.add(cell.getStringCellValue());
        }
        pkg.close();
        return header;
    }

    private static class SheetHandler extends DefaultHandler {
        private SharedStringsTable sst;
        private String lastContents;
        private boolean nextIsString;
        private List<String> rowList = new ArrayList<>();
        private int curRow = 0; //当前行

        private int curCol = 0; //当前列索引

        private int preCol = 0; //上一列列索引

        private int rowsize = 0; //列数

        private List<List<String>> dataList;


        public SheetHandler(SharedStringsTable sst, List<List<String>> dataList) {
            this.sst = sst;
            this.dataList = dataList;
        }

        @Override
        public void startDocument() throws SAXException {
            super.startDocument();
        }

        @Override
        public void endDocument() throws SAXException {
            if(curRow <= 1){
                throw new SAXParseException("导入文件不能为空", null);
            }
        }


        @Override
        public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException {
            // c => 单元格
            if ("c".equals(name)) {
                // 如果下一个元素是 SST 的索引，则将nextIsString标记为true
                String cellType = attributes.getValue("t");
                String rowStr = attributes.getValue("r");
                curCol = this.getRowIndex(rowStr);
                nextIsString = "s".equals(cellType);
            }
            // 置空
            lastContents = "";
        }

        @Override
        public void endElement(String uri, String localName, String name) throws SAXException {
            // 根据SST的索引值的到单元格的真正要存储的字符串
            // 这时characters()方法可能会被调用多次
            if (nextIsString) {
                try {
                    int idx = Integer.parseInt(lastContents);
                    lastContents = new XSSFRichTextString(sst.getEntryAt(idx)).toString();
                } catch (Exception ignored) {
                }
            }
            // v => 单元格的值，如果单元格是字符串则v标签的值为该字符串在SST中的索引
            // 将单元格内容加入rowlist中，在这之前先去掉字符串前后的空白符
            if ("v".equals(name)) {
                String value = lastContents.trim();
                value = "".equals(value) ? " " : value;
                int cols = curCol - preCol;
                if (cols > 1) {
                    for (int i = 0; i < cols - 1; i++) {
                        rowList.add(preCol, "");
                    }
                }
                preCol = curCol;
                rowList.add(curCol - 1, value);
            } else {
                //如果标签名称为 row ，这说明已到行尾，调用 optRows() 方法
                if ("row".equals(name)) {
                    int tmpCols = rowList.size();
                    //标题行，一般情况下为0
                    int titleRow = 0;
                    if (curRow > titleRow && tmpCols < this.rowsize) {
                        for (int i = 0; i < this.rowsize - tmpCols; i++) {
                            rowList.add(rowList.size(), "");
                        }
                    }
                    if (curRow > titleRow) {
                        long count = rowList.stream().filter(StringUtils::isBlank).count();
                        if(count != rowList.size()){
                            dataList.add(rowList);
                        }
                    }
                    if (curRow == titleRow) {
                        this.rowsize = rowList.size();
                    }
                    rowList = new ArrayList<>();
                    curRow++;
                    curCol = 0;
                    preCol = 0;
                }
            }
        }

        @Override
        public void characters(char[] ch, int start, int length) throws SAXException {
            lastContents += new String(ch, start, length).trim();
        }

        public int getRowIndex(String rowStr) {
            rowStr = rowStr.replaceAll("[^A-Z]", "");
            byte[] rowAbc = rowStr.getBytes();
            int len = rowAbc.length;
            float num = 0;
            for (int i = 0; i < len; i++) {
                num += (rowAbc[i] - 'A' + 1) * Math.pow(26D, len - i - 1D);
            }
            return (int) num;
        }
    }
}